import re
from myapp.utils.connection import POOL
import json


def ageToJson(kw, mid=''):
    conn = POOL.connection()
    cur = conn.cursor()
    sql = ''
    if mid == '':
        sql = "SELECT birthday FROM {}_userspider".format(kw)
    else:
        # 根据根节点筛选
        sql = "SELECT u.birthday FROM {}_userspider u,{}_findson f WHERE f.rootknot = {} AND f.userid = u.uid".format(
            kw, kw, mid)
    cur.execute(sql)

    result = { '10-20岁': 0, '20-30岁': 0,
              '30-40岁': 0, '40-50岁': 0, '50岁以上': 0}
    data = cur.fetchall()
    xingzuo = re.compile("座")

    for sub_text in data:
        if sub_text[0] is None:
            year = 0
        elif xingzuo.search(sub_text[0]):
            year = 0
        elif sub_text[0].split("-")[0]:
            year = int(sub_text[0].split("-")[0])
        else:
            year = 0
        age = 2020 - year
        if age >= 100:
            continue
        if age >= 10 and age < 20:
            ageOfPerson = '10-20岁'
        if age >= 20 and age < 30:
            ageOfPerson = '20-30岁'
        if age >= 30 and age < 40:
            ageOfPerson = '30-40岁'
        if age >= 40 and age < 50:
            ageOfPerson = '40-50岁'
        if age >= 50 and age < 100:
            ageOfPerson = '50岁以上'
        result[ageOfPerson] += 1

    final = {"data": [{"value": result['10-20岁'], 'name': '10-20岁'},
                      {'value': result['20-30岁'], 'name': '20-30岁'},
                      {'value': result['30-40岁'], 'name': '30-40岁'},
                      {'value': result['40-50岁'], 'name': '40-50岁'},
                      {'value': result['50岁以上'], 'name': '50岁以上'}
                      ]}
    cur.close()
    conn.close()
    # jsondata = json.dumps(final, ensure_ascii=False)
    # f = open('agejson.json', 'w', encoding='utf-8')
    # f.write(jsondata)
    # f.close()
    return final

#    return result #类似这样的输出{'不详': 167, '10-20岁': 4, '20-30岁': 42, '30-40岁': 34, '40-50岁': 11, '50岁以上': 10}

# ageToJson('企业复工')